﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

using System.Diagnostics;
using System.Collections;

namespace dmm
{
    
    class DBAcess
    {
        private SQLiteConnection con = new SQLiteConnection();
        private string path = Options.Instance.DBFile;


        private static Int64 max_id=0;

        public bool db_create(){

            try
            {
                SQLiteConnection.CreateFile(path);

                con.ConnectionString = "Data Source=" + path + "; FailIfMissing=True";
                con.Open();

                SQLiteCommand cmd = new SQLiteCommand();
                string sql = "create VIRTUAL TABLE videos using fts3(name varchar(100)," +
                                             "path varchar(256),"+ //local path
                                             "srctype string,"+  //source type:record/local/convert
                                             "url  varchar(512),"+ //record page url
                                             "thumbnail varchar(256),"+//thumbnail
                                             "duration real,"+     //in seconds
                                             "create_date  char(20),"+
                                             "width integer,"+
                                             "height integer,"+
                                             "profile varchar(255),"+  //convert profile used
                                             "container varchar(100),"+
                                             "vcodec varchar(100),"+
                                             "acodec varchar(100),"+
                                             "desc varchar(512))";
                cmd.CommandText = sql;
                cmd.Connection = con;
                cmd.ExecuteNonQuery();

                /*sql = "create FULLTEXT INDEX ft_video ON  'videos'(name,path,url,thumbnail,profile,desc)";
                cmd.CommandText = sql;
                cmd.Connection = con;
                cmd.ExecuteNonQuery();*/

            }
            catch (SQLiteException e)
            {
                Debug.Write("create database failed:" + e);
            }
            return true;
        }

       
        public bool db_connect()
        {

            try
            {
                if (con.State != System.Data.ConnectionState.Open)
                {
                    con.ConnectionString = "Data Source=" + path + "; FailIfMissing=True";
                    con.Open();
                }
                
            }
            catch (SQLiteException )
            {
                Debug.Write("database file not exist,trying to create...");
                db_create();

                //add_video(VideoSourceType.VideoFromLocal,"video1", "g:\\tv\\1.flv",@"G:\work\demomaker\test\test.cap.avi.bmp",190,240,10);
               // add_video(VideoSourceType.VideoFromLocal, "clock", "g:\\tv\\clock.avi", @"G:\work\demomaker\test\test.cap.avi.bmp", 321, 321,10);
            }
            return true;
        }

        public bool db_close()
        {
            con.Close();
            return true;
        }
        public bool add_video(Video v)
        {

            try
            {
                SQLiteCommand cmd = new SQLiteCommand();

                string sql = String.Format(@"insert into videos(name,path,url,thumbnail,duration,create_date,width,height,container,vcodec,acodec,desc,srctype,profile) values(" +
                                            "'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}');",
                                            v.Name, v.Path, v.Url, v.Thumbnail, v.Duration, v.CreateDate, v.Width, v.Height, v.Container, v.Vcodec, v.Acodec, v.Desc,v.Srctype,v.Profile);
                cmd.CommandText = sql;
            
                cmd.Connection = con;
                
                int res = cmd.ExecuteNonQuery();
                if (res != 1)
                {
                    Debug.Write("insert error");
                }

            }
            catch (SQLiteException e)
            {
                Debug.Write("add video to db error" + e);
                return false;
            }
            return true;
        }


        public bool delete_video(Int64 id)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();

                string sql = String.Format(@"delete from videos where rowid={0}",id);
                cmd.CommandText = sql;
                cmd.Connection = con;
                int res = cmd.ExecuteNonQuery();
                if (res != 1)
                {
                    Debug.Write("update error");
                }

            }
            catch (SQLiteException e)
            {
                Debug.Write(e);
                System.Windows.MessageBox.Show("Database error");
                return false;
            }
            return true;
        }

        public bool update_video(Video v)
        {

            try
            {
                SQLiteCommand cmd = new SQLiteCommand();

                string sql = String.Format(@"update videos set name='{0}',path='{1}',url='{2}',thumbnail='{3}',duration='{4}',create_date='{5}',width='{6}',height='{7}',container='{8}',vcodec='{9}',acodec='{10}',desc='{11}',srctype='{12}',profile='{13}' where rowid={14}",
                                            v.Name, v.Path, v.Url, v.Thumbnail, v.Duration, v.CreateDate, v.Width, v.Height, v.Container, v.Vcodec, v.Acodec, v.Desc, v.Srctype, v.Profile,v.ID);
                cmd.CommandText = sql;
                cmd.Connection = con;
                int res = cmd.ExecuteNonQuery();
                if (res != 1)
                {
                    Debug.Write("update error");
                }

            }
            catch (SQLiteException e)
            {
                Debug.Write(e);
                System.Windows.MessageBox.Show("Database error");
                return false;
            }
            return true;
        }

        public bool add_video(VideoSourceType srctype,string name,string path,string thumbnail,int width,int height,double duration)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();

                cmd.CommandText = String.Format("insert into videos(name,path,thumbnail,width,height,duration,srctype) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", name, path, thumbnail, width, height, duration, srctype);
                cmd.Connection = con;
                cmd.ExecuteNonQuery();
            }
            catch (SQLiteException e)
            {
                Debug.Write(e);
                System.Windows.MessageBox.Show("Database error");
                return false;
            }
            catch (Exception e)
            {
                Debug.Write(e);
            }

            return true;
        }

        public Int64 get_id()
        {
            max_id += 1;
            return max_id;
        }
        public void load_videos(ArrayList lst, string filter)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();

                if(filter == String.Empty)
                    cmd.CommandText = String.Format("select name,path,url,thumbnail,duration,create_date,width,height,container,vcodec,acodec,desc,rowid,srctype,profile from videos");
                else
                    cmd.CommandText = String.Format("select name,path,url,thumbnail,duration,create_date,width,height,container,vcodec,acodec,desc,rowid,srctype,profile from videos where videos MATCH '{0}'",filter+"*");

                cmd.Connection = con;
                SQLiteDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {

                    Video video = new Video();
                    //video.load_video(reader.GetString(0),reader.GetString(1));
                    if (!reader.IsDBNull(0)) video.Name = reader.GetString(0);
                    if (!reader.IsDBNull(1)) video.Path = reader.GetString(1);
                    if (!reader.IsDBNull(2)) video.Url = reader.GetString(2);
                    if (!reader.IsDBNull(3)) video.Thumbnail = reader.GetString(3);
                    //if (!reader.IsDBNull(4)) video.Duration = reader.GetDouble(4);
                    if (!reader.IsDBNull(4)) video.Duration = double.Parse(reader.GetString(4));
                    if (!reader.IsDBNull(5)) video.CreateDate = reader.GetString(5);
                   // if (!reader.IsDBNull(6)) video.Width = reader.GetInt32(6);
                    if (!reader.IsDBNull(6)) video.Width = Int32.Parse(reader.GetString(6));


                    //if (!reader.IsDBNull(7)) video.Height = reader.GetInt32(7);
                    if (!reader.IsDBNull(7)) video.Height = Int32.Parse(reader.GetString(7));
                    if (!reader.IsDBNull(8)) video.Container = reader.GetString(8);
                    if (!reader.IsDBNull(9)) video.Vcodec = reader.GetString(9);
                    if (!reader.IsDBNull(10)) video.Acodec = reader.GetString(10);
                    if (!reader.IsDBNull(11)) video.Desc = reader.GetString(11);
                    if (!reader.IsDBNull(12)) video.ID = reader.GetInt64(12);
                    //if (!reader.IsDBNull(12)) video.ID = Int64.Parse(reader.GetString(Int64(12);
                    if (!reader.IsDBNull(13)) video.Srctype = (VideoSourceType)System.Enum.Parse(typeof(VideoSourceType), reader.GetString(13)); ;

                    if (!reader.IsDBNull(14)) video.Profile = reader.GetString(14);

                    max_id = Math.Max(max_id, video.ID);
                    video.IsDirty = false;
                    lst.Add(video);

                }
            }
            catch (SQLiteException e)
            {
                System.Windows.MessageBox.Show("Database error");
                Debug.Write("add video to db error" + e);

            }
        }
        public void load_videos(ArrayList lst)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();

                cmd.CommandText = String.Format("select name,path,url,thumbnail,duration,create_date,width,height,container,vcodec,acodec,desc,rowid,srctype,profile from videos");
                cmd.Connection = con;
                SQLiteDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {

                    Video video = new Video();
                    //video.load_video(reader.GetString(0),reader.GetString(1));
                    if(!reader.IsDBNull(0)) video.Name = reader.GetString(0);
                    if (!reader.IsDBNull(1)) video.Path = reader.GetString(1);
                    if (!reader.IsDBNull(2)) video.Url = reader.GetString(2);
                    if (!reader.IsDBNull(3)) video.Thumbnail = reader.GetString(3);
                    if (!reader.IsDBNull(4)) video.Duration = reader.GetDouble(4);
                    if (!reader.IsDBNull(5)) video.CreateDate = reader.GetString(5);
                    if (!reader.IsDBNull(6)) video.Width = reader.GetInt32(6);

                    if (!reader.IsDBNull(7)) video.Height = reader.GetInt32(7);
                    if (!reader.IsDBNull(8)) video.Container = reader.GetString(8);
                    if (!reader.IsDBNull(9)) video.Vcodec = reader.GetString(9);
                    if (!reader.IsDBNull(10)) video.Acodec = reader.GetString(10);
                    if (!reader.IsDBNull(11)) video.Desc = reader.GetString(11);
                    if (!reader.IsDBNull(12)) video.ID = reader.GetInt64(12);
                    if (!reader.IsDBNull(13)) video.Srctype = (VideoSourceType)System.Enum.Parse(typeof(VideoSourceType), reader.GetString(13)); ;
                    
                    if (!reader.IsDBNull(14)) video.Profile = reader.GetString(14);

                    max_id = Math.Max(max_id, video.ID);
                    video.IsDirty = false;
                    lst.Add(video);

                }
            }
            catch (SQLiteException e)
            {
                System.Windows.MessageBox.Show("Database error");
                Debug.Write("add video to db error" + e);
             
            }

        }
    }
}
